package com.pdweb.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.pdweb.vo.Divisoes;

public class DivisoesDao extends DBconnection{
	
	private Connection conn;

	public DivisoesDao() throws ClassNotFoundException, SQLException {
		this.conn = getConnection();
	}

	public DivisoesDao(Connection conn) {
		this.conn = conn;
	}

	public void adicionaDivisao(Divisoes divisoes) throws SQLException {

		PreparedStatement st = null;

		try {
			st = conn
					.prepareStatement("insert into Divisoes(ST_NOMEDIVISAO_DIV, ST_RESPONSAVEL_DIV, empresas_ID_EMPRESA_emp) value(?,?,?);");

			st.setString(1, divisoes.getNomeDivisao());
			st.setString(2, divisoes.getNomeResponsavel());
			st.setInt(3, divisoes.getIdEmpresa());
			st.execute();
		} finally {
			st.close();
		}
	}

	public List<Divisoes> obterDivisoes() throws SQLException {

		List<Divisoes> divisoes = new ArrayList<Divisoes>();

		Statement st = null;
		ResultSet rs = null;

		try {
			st = conn.createStatement();
			rs = st.executeQuery("select id_divisao_div, empresas.st_nome_emp, st_nomedivisao_div, st_responsavel_div from divisoes inner join empresas on divisoes.empresas_id_empresa_emp = empresas.id_empresa_emp order by id_divisao_div" );
			while (rs.next()) {
				Divisoes result = new Divisoes();
				result.setIdDivisao(rs.getInt("ID_DIVISAO_DIV"));
				
				result.setNomeEmpresa(rs.getString("empresas.st_nome_emp"));
				result.setNomeDivisao(rs.getString("ST_NOMEDIVISAO_DIV"));
				result.setNomeResponsavel(rs.getString("ST_RESPONSAVEL_DIV"));

				divisoes.add(result);
			}
		} finally {
			st.close();
			rs.close();
		}
		return divisoes;
	}

	public void excluirDivisao(Divisoes divisoes) throws SQLException {

		PreparedStatement st = null;

		try {
			st = conn
					.prepareStatement("delete from Divisoes where ID_DIVISAO_DIV = ?;");

			st.setInt(1, divisoes.getIdDivisao());
			st.execute();
		} finally {
			st.close();
		}
	}

	public Divisoes obterDivisao(Divisoes divisoes) throws SQLException {
		Divisoes retorno = null;
		Statement st = null;
		ResultSet rs = null;

		try {
			st = conn.createStatement();
			
			String sql = "select * from Divisoes where ID_DIVISAO_DIV = "
					+ divisoes.getIdDivisao();

			rs = st.executeQuery(sql);

			if (rs.next()) {
				retorno = new Divisoes();

				retorno.setIdDivisao(rs.getInt("ID_DIVISAO_DIV"));
				retorno.setIdEmpresa(rs.getInt("empresas_ID_EMPRESA_emp"));
				retorno.setNomeDivisao(rs.getString("ST_NOMEDIVISAO_DIV"));
				retorno.setNomeResponsavel(rs.getString("ST_RESPONSAVEL_DIV"));
			}
			return retorno;
		} finally {
			st.close();
			rs.close();
		}
	}
	
	public void atualizaDivisao(Divisoes divisoes) throws SQLException{
		
		PreparedStatement st = null;
		try {
			st = conn
					.prepareStatement("UPDATE divisoes SET empresas_ID_EMPRESA_emp = ? ,ST_NOMEDIVISAO_DIV = '?' ,ST_RESPONSAVEL_DIV = '?' WHERE ID_DIVISAO_DIV = ?;");
			
			st.setInt(1, divisoes.getIdEmpresa());
			st.setString(2, divisoes.getNomeDivisao());
			st.setString(3, divisoes.getNomeResponsavel());
			st.setInt(4, divisoes.getIdDivisao());
			
			st.execute();
		} finally {
			st.close();

		}
	}
	
	public List<Divisoes> consultaDivisoes(String sql) throws SQLException {

		List<Divisoes> divisoes = new ArrayList<Divisoes>();

		Statement st = null;
		ResultSet rs = null;

		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			
			while (rs.next()) {
				
				Divisoes result = new Divisoes();
				
				result.setIdDivisao(rs.getInt("ID_DIVISAO_DIV"));
				result.setNomeEmpresa(rs.getString("empresas.st_nome_emp"));
				result.setNomeDivisao(rs.getString("ST_NOMEDIVISAO_DIV"));
				result.setNomeResponsavel(rs.getString("ST_RESPONSAVEL_DIV"));

				divisoes.add(result);
				
			}
		} finally {
			st.close();
			rs.close();
		}
		return divisoes;
	}
	
	public boolean verificaDivisao(int empresaId) throws SQLException {

		Statement st = null;
		ResultSet rs = null;

		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from divisoes where empresas_ID_EMPRESA_emp = "+empresaId+"; ");
			if (!rs.next()) {
				return false;
			} else {
				return true;
			}
		} finally {
			st.close();
			rs.close();
		}
	}
	
	public List<Divisoes> listaDivisoesPorEmpresa(String sql) throws SQLException{
		
		List<Divisoes> divisoes = new ArrayList<Divisoes>();

		Statement st = null;
		ResultSet rs = null;

		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			
			while (rs.next()) {
				
				Divisoes result = new Divisoes();
				
				result.setIdDivisao(rs.getInt("ID_DIVISAO_DIV"));
				
				result.setNomeDivisao(rs.getString("ST_NOMEDIVISAO_DIV"));
				result.setNomeResponsavel(rs.getString("ST_RESPONSAVEL_DIV"));

				divisoes.add(result);
				
			}
		} finally {
			st.close();
			rs.close();
		}
		return divisoes;
	}
	

}
